package com.feib.soeasy.service.impl;


import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.feib.soeasy.dao.CommissionCodeTableDao;
import com.feib.soeasy.dao.Soez6001Dao;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.pagination.PaginatedList;
import com.feib.soeasy.service.Soez6001Service;
import com.feib.soeasy.util.CodeTableUtil;
import com.feib.soeasy.util.UserUtil;



@Service("soez6001Service")
public class Soez6001ServiceImpl extends BaseServiceImpl implements Soez6001Service {


	protected Logger logger = LoggerFactory.getLogger(this.getClass());


	@Resource(name="soez6001Dao")
	private Soez6001Dao soez6001Dao;


	@Resource(name="commissionCodeTableDao")
	public CommissionCodeTableDao commissionCodeTableDao;


	/**
	 * 暫存目錄
	 */
	@Resource(name = "tempDirectory")
	private String tempDirectory;

	/**
	 * 傳輸日期  起
	 */
	private Date qryDateMin;

	/**
	 *傳輸日期 訖
	 */
	private Date qryDateMax;



	/**
	 * 取得各通路已撥款放大鏡明細
	 * @author Jeff  Gu 
	 * */	
	public PaginatedList<Map<String, Object>> queryGroupFeedbackLogByChannel(Group group, String channels[],
			Date dateFroms[], Date dateTos[],List<String> queryGroupType,String transfered,String accountNo, PaginatedList<Map<String, Object>> paginationConfig) {
		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		PaginatedList<Map<String, Object>> fbLogs = soez6001Dao.queryTotalGroupFeedbackLog(group, channels, dateFroms, dateTos, queryGroupType, transfered, accountNo, paginationConfig);

		if(fbLogs.getPaginatedList()== null || fbLogs.getPaginatedList().size() == 0)
			return null;

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		/* 設定複製用PaginatedList 再對資料作處理 不然 foreach的狀況下不能對來源檔案作操作  */
		PaginatedList<Map<String, Object>> results = new PaginatedList<Map<String, Object>>();				
		results.setFullListSize(fbLogs.getFullListSize());
		results.setPageSize(fbLogs.getPageSize());
		results.setPageNumber(fbLogs.getPageNumber());
		results.setSortCriterion(fbLogs.getSortCriterion());
		results.setSortDirection(fbLogs.getSortDirection());

		List<Map<String, Object>> temp = new ArrayList<Map<String, Object>>();
		for(Map<String, Object> fblog:fbLogs.getPaginatedList())
		{	
			/* 總計明細用的總計 顯示在最下方,最後一頁再顯示 */
			if(fbLogs.isLastPage()){	
				@SuppressWarnings("unchecked")
				Map<String, Object> total=(Map<String, Object>) fbLogs.getTotalObject();
				fblog.put("totalCount", total.get("totalCount"));
				fblog.put("totalAmount", ((BigDecimal)total.get("totalAmount")).longValue());
				fblog.put("totalFee", ((BigDecimal)total.get("totalFee")).longValue());
			}
			/*計算總計*/
			temp.add(fblog);

		}

		results.setPaginatedList(temp);
		/* 清除資源*/
		fbLogs.getPaginatedList().clear();
		fbLogs=null;

		return results;
	}


	/**
	 *查詢主畫面excel 
	 */
	@Override
	public String doExportGroupStatisticsXls(Group group, String[] channels,
			Date[] dateFroms, Date[] dateTos,List<String> gueryGroupType,String transfered) {

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商撥款統計表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*25);
		sheet.setColumnWidth(1, 256*10);
		sheet.setColumnWidth(3, 256*20);
		sheet.setColumnWidth(4, 256*20);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*20);


		// 行數
		int rownum = 0;

		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局撥款統計表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));

		logger.debug("超商／郵局撥款統計表 {}",rownum);

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		logger.debug("blankRow {}",rownum);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			if(StringUtils.isNotBlank(channel))
			{
				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel)  + "：" + dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("title2"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$H$" + rownum));
			}

		}

		logger.debug("channels {}",rownum);

		//串欄位表頭
		String[] titles = {"NO",
				"撥款日期","業者代號","實體帳號","業者名稱","通路" ,"代收筆數", "入帳金額"};

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;

		logger.debug("titles {}",rownum);

		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		//總計數據
		Long totalCount = new Long(0);
		Long totalRealAmount = new Long(0);


		PaginatedList<Map<String, Object>>queryResult = new PaginatedList<Map<String,Object>>();
		/* 保持顯示全部不分頁*/
		queryResult.setPageSize(-1);

		PaginatedList<Map<String, Object>> result = soez6001Dao.queryTransferLogByGroup(group, channels, dateFroms, dateTos, gueryGroupType, transfered,queryResult);

		List<Map<String, Object>> fbLogs = result.getPaginatedList();

		int no=0;//序號

		if (null != fbLogs && 0 != fbLogs.size())
		{	int cellRangeAddressStart=0;
		for(Map<String, Object> channelData : fbLogs)
		{	

			no++;

			int cellRangeAddressEnd = 0;
			cellRangeAddressStart=rownum+1;	
			for(int i=0;i<channels.length;i++)
			{
				String channel = channels[i];
				if(StringUtils.isNotBlank(channel))
				{

					Row dataRow = null; 
					Cell cell = null;
					//int mergeRownum = sheet.getLastRowNum() + 1;
					/*計算合併欄位的位置*/



					cellRangeAddressEnd = 0;

					/* 一圈就是一個日期區間的資料 */


					Date transferTime = (Date)channelData.get("transferTime");//撥款日期
					String groupNo = (String)channelData.get("groupNo");//業者代號
					String  accountNo= (String)channelData.get("accountNo");//實體帳號
					String  groupName= (String)channelData.get("groupName");//業者名稱
					//String channel = (String)channelData.get("CHANNEL");        	
					//String channelName = (String)channelData.get("CHANNEL_NAME");//通路名稱



					dataRow = sheet.createRow(rownum++);
					if (0>= cellRangeAddressEnd)
						cellRangeAddressEnd = rownum;
					dataRow.setHeightInPoints((float) 25);


					cell = dataRow.createCell(0);//序號
					cell.setCellValue(no);
					cell.setCellStyle(styles.get("cell"));


					cell = dataRow.createCell(1);//撥款日期
					if (transferTime==null)
						cell.setCellValue("N/A");
					else
						cell.setCellValue(dateFormat.format(transferTime));
					cell.setCellStyle(styles.get("cell_date"));

					cell = dataRow.createCell(2);//業者代號
					cell.setCellValue(groupNo);
					cell.setCellStyle(styles.get("cell"));

					cell = dataRow.createCell(3);//實體帳號
					cell.setCellValue(accountNo);
					cell.setCellStyle(styles.get("cell"));

					cell = dataRow.createCell(4);//業者名稱
					cell.setCellValue(groupName);
					cell.setCellStyle(styles.get("cell"));





					/*超商欄位*/
					String  codeName = CodeTableUtil.getInstance().getCodeName("CHANEL", channel) ;//通路名稱

					// 沒有的不顯示
					int channelCount = (int)((BigDecimal) channelData.get(channel+"Count")).longValue();
					Long channelAmount = ((BigDecimal)channelData.get(channel+"Amount")).longValue();

					cell = dataRow.createCell(5);
					cell.setCellValue(codeName);
					cell.setCellStyle(styles.get("cell"));

					if(channelCount>0){
						cell = dataRow.createCell(6);
						cell.setCellValue(channelCount);
						cell.setCellStyle(styles.get("cell"));

						cell = dataRow.createCell(7);
						cell.setCellValue(channelAmount);
						cell.setCellStyle(styles.get("cell_number"));
					}else{
						cell = dataRow.createCell(6);

						cell.setCellValue("查無資料 ");
						cell.setCellStyle(styles.get("cell"));
						cell = dataRow.createCell(7);
						cell.setCellStyle(styles.get("cell"));
						sheet.addMergedRegion(CellRangeAddress.valueOf("$G$"+rownum+":$H$" + rownum));

					}


				}



			}

			/*合併前五格*/
			String[] mergeRow={"A","B","C","D","E"};	

			for(int i=0;i<=4;i++){
				//ex:"$A$"+rownum+":$I$" + rownum  "A$9"
				//sheet.addMergedRegion(CellRangeAddress.valueOf("$"+mergeRow[0]+"$"+cellRangeAddress+":$"+mergeRow[0]+"$"+(cellRangeAddress+lastIndex)));            
				sheet.addMergedRegion(CellRangeAddress.valueOf("$"+mergeRow[i]+"$"+(cellRangeAddressStart)+":$"+mergeRow[i]+"$"+(cellRangeAddressEnd)));            
			}



			/*統計*/
			Row dataCountRow = sheet.createRow(rownum++);
			Cell dataCell;
			dataCell = dataCountRow.createCell(0);
			dataCell.setCellValue("統計");
			dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
			dataCell = dataCountRow.createCell(1);
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataCountRow.createCell(2);
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataCountRow.createCell(3);	
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataCountRow.createCell(4);	
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataCountRow.createCell(5);				
			dataCell.setCellStyle(styles.get("cell"));

			int groupTotalCount =(int)((BigDecimal) channelData.get("totalCount")).longValue();
			Long groupRealAmount = ((BigDecimal)channelData.get("realAmount")).longValue();

			dataCell = dataCountRow.createCell(6);
			dataCell.setCellValue(groupTotalCount);
			dataCell.setCellStyle(styles.get("cell"));

			dataCell = dataCountRow.createCell(7);
			dataCell.setCellValue(groupRealAmount);
			dataCell.setCellStyle(styles.get("cell_number"));

			/* 統計所有數據 */
			totalCount += groupTotalCount;
			totalRealAmount += groupRealAmount;



			//合併
			sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$F$" + rownum));

		}

		}

		/* 總計*/
		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));
		dataCell = dataRow.createCell(0);
		dataCell.setCellValue("統計");
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		dataCell = dataRow.createCell(1);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(2);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(3);	
		dataCell.setCellStyle(styles.get("cell"));

		dataCell = dataRow.createCell(4);				
		dataCell.setCellValue("超商／郵局合計");
		dataCell.setCellStyle(styles.get("cell"));

		dataCell = dataRow.createCell(5);				
		dataCell.setCellStyle(styles.get("cell"));
		/*  總計記數 */		
		dataCell = dataRow.createCell(6);
		dataCell.setCellValue(totalCount);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(7);
		dataCell.setCellValue(totalRealAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-超商傳輸統計表.xls"; //

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}



	@Override
	public String doExportGroupDetailStatisticsXls(Group group,
			String[] channels, Date[] dateFroms, Date[] dateTos,List<String> queryGroupType,String accountNo, String transfered) {
		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商郵局撥款統計明細表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*25);
		sheet.setColumnWidth(1, 256*10);
		sheet.setColumnWidth(3, 256*20);
		sheet.setColumnWidth(4, 256*20);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*20);


		// 行數
		int rownum = 0;

		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商/郵局撥款統計明細表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		/* 顯示通路跟區間*/
		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			if(channel != null && !"".equals(channel))
			{
				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel)  + "：" + dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("title2"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$H$" + rownum));
			}

		}

		//串欄位表頭
		String[] titles =null;


		if("0".equals(transfered)){

			titles =new String []{"傳輸日期","CODE","繳費期限","繳費日期" ,"帳單業者", "繳費帳號", "繳費金額" , "代收手續費","實撥金額"};
		}else if("1".equals(transfered)){

			titles =new String []{"傳輸日期","撥款日期","CODE","繳費期限","繳費日期" ,"帳單業者", "繳費帳號", "繳費金額" , "代收手續費","實撥金額"};
		}



		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;

		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		//總計數據
		int totalCount = 0;
		Long totalAmount = new Long(0);
		Long totalFee = new Long(0);
		Long totalRealAmount = new Long(0);




		PaginatedList<Map<String, Object>>queryResult = new PaginatedList<Map<String,Object>>();
		/* 單通路單代碼明細資料保持顯示全部不分頁*/
		queryResult.setPageSize(-1);
		PaginatedList<Map<String, Object>> result = soez6001Dao.queryTotalGroupFeedbackLog(group, channels, dateFroms, dateTos, queryGroupType, transfered, accountNo, queryResult);

		List<Map<String, Object>> fbLogs = result.getPaginatedList();
		/*產生傳輸日期區間*/
		compareDate(channels,dateFroms, dateTos );


		for(Map<String, Object>fblog:fbLogs)
		{

			long txamt = ((BigDecimal)fblog.get("txamt")).longValue();  

			long fee =((BigDecimal)fblog.get("fee")).longValue();					

			Row dataRow = sheet.createRow(rownum++);
			Cell dataCell;
			dataCell = dataRow.createCell(0);

			/*show fbtime*/
			int showFbtimeNum=1;

			Date transferTime = (Date)fblog.get("transferTime");
			Date fbTime = (Date)fblog.get("fbtime");


			if("0".equals(transfered)){

				if (fbTime==null)
					dataCell.setCellValue("N/A");
				else
					dataCell.setCellValue(dateFormat.format(fbTime));
				dataCell.setCellStyle(styles.get("cell_date"));
				dataCell = dataRow.createCell(showFbtimeNum++);

			}else if("1".equals(transfered)){

			
				if (fbTime==null)
					dataCell.setCellValue("N/A");
				else
					dataCell.setCellValue(dateFormat.format(fbTime));
				dataCell.setCellStyle(styles.get("cell_date"));
				dataCell = dataRow.createCell(showFbtimeNum++);
				
				
				if (transferTime==null)
					dataCell.setCellValue("N/A");
				else
					dataCell.setCellValue(dateFormat.format(transferTime));
				dataCell.setCellStyle(styles.get("cell_date"));
				dataCell = dataRow.createCell(showFbtimeNum++);


			}

			
			dataCell.setCellValue((String)fblog.get("code"));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(showFbtimeNum++);

			Date dueday = (Date)fblog.get("dueday");
			if (dueday==null)
				dataCell.setCellValue("N/A");
			else
				dataCell.setCellValue(dateFormat.format(dueday));

			dataCell.setCellStyle(styles.get("cell_date"));
			dataCell = dataRow.createCell(showFbtimeNum++);
			
			String aday = (String)fblog.get("aday");
			dataCell.setCellValue(aday.substring(0,4)+"/"+aday.substring(4,6)+"/"+aday.substring(6,8));
			dataCell.setCellStyle(styles.get("cell_date"));
			dataCell = dataRow.createCell(showFbtimeNum++);
			
			dataCell.setCellValue((String)fblog.get("groupname"));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(showFbtimeNum++);
			
			dataCell.setCellValue((String)fblog.get("userno"));
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataRow.createCell(showFbtimeNum++);
			
			dataCell.setCellValue(txamt);
			dataCell.setCellStyle(styles.get("cell_number"));
			dataCell = dataRow.createCell(showFbtimeNum++);
			
			dataCell.setCellValue(fee);
			dataCell.setCellStyle(styles.get("cell_number"));
			dataCell = dataRow.createCell(showFbtimeNum++);
			
			dataCell.setCellValue(txamt-fee);
			dataCell.setCellStyle(styles.get("cell_number"));


		}

		@SuppressWarnings("unchecked")
		Map<String, Object> total=(Map<String, Object>) result.getTotalObject();

		totalCount = (Integer) total.get("totalCount");
		totalAmount = ((BigDecimal)total.get("totalAmount")).longValue();
		totalFee = ((BigDecimal)total.get("totalFee")).longValue();
		totalRealAmount =totalAmount-totalFee;



		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;
		/*show fbtime*/
		int showFbtimeNum=4;
	
			dataCell = dataRow.createCell(0);
			dataCell.setCellValue("總計");
			dataCell.setCellStyle(styles.get("cell"));
			
			dataCell = dataRow.createCell(1);		
			dataCell.setCellStyle(styles.get("cell"));
			
			dataCell = dataRow.createCell(2);			
			dataCell.setCellStyle(styles.get("cell"));
		
			dataCell = dataRow.createCell(3);		
			dataCell.setCellStyle(styles.get("cell"));
			
			dataCell = dataRow.createCell(4);
			dataCell.setCellStyle(styles.get("cell"));
						
		if("0".equals(transfered)){	
			sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$E$" + rownum));
			
		}else if("1".equals(transfered)){
			
			dataCell = dataRow.createCell(++showFbtimeNum);
			dataCell.setCellStyle(styles.get("cell"));
			sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$F$" + rownum));
		}
		dataCell = dataRow.createCell(++showFbtimeNum);
		dataCell.setCellValue(totalCount+"筆");
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		logger.debug("showFbtimeNum {}",showFbtimeNum);
		dataCell = dataRow.createCell(++showFbtimeNum);
		dataCell.setCellValue(totalAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		logger.debug("showFbtimeNum {}",showFbtimeNum);
		dataCell = dataRow.createCell(++showFbtimeNum);
		dataCell.setCellValue(totalFee);
		dataCell.setCellStyle(styles.get("cell_number"));
		logger.debug("showFbtimeNum {}",showFbtimeNum);
		dataCell = dataRow.createCell(++showFbtimeNum);
		dataCell.setCellValue(totalRealAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		logger.debug("showFbtimeNum {}",showFbtimeNum);


		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-超商傳輸統計表.xls"; //

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}

	/**
	 * Create a library of cell styles
	 */
	private static Map<String, CellStyle> createStyles(Workbook wb){
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		CellStyle style;
		Font titleFont = wb.createFont();
		titleFont.setFontName("標楷體");
		titleFont.setFontHeightInPoints((short)24);
		style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFont(titleFont);
		styles.put("title", style);

		Font title2Font = wb.createFont();
		title2Font.setFontName("標楷體");
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題
		title2Font.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(title2Font);        
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		styles.put("title2", style);

		Font headerFont = wb.createFont();
		headerFont.setFontName("標楷體");
		headerFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(headerFont);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("header", style);

		Font cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)12);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell", style);

		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setWrapText(false);//設定文字是否換行
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_ALIGN_RIGHT", style);

		style = wb.createCellStyle();
		Font cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number", style);

		style = wb.createCellStyle();
		Font cellDateFont = wb.createFont();
		cellDateFont.setFontName("Arial");
		cellDateFont.setFontHeightInPoints((short)12);
		style.setFont(cellDateFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
		styles.put("cell_date", style);


		cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)18);
		cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_sub_total", style);

		style = wb.createCellStyle();
		cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number_sub_total", style);




		cellFont = wb.createFont();
		cellFont.setFontName("標楷體");
		cellFont.setFontHeightInPoints((short)14);
		cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell_total", style);

		style = wb.createCellStyle();
		cellNumberFont = wb.createFont();
		cellNumberFont.setFontName("Arial");
		cellNumberFont.setFontHeightInPoints((short)14);
		cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style.setFont(cellNumberFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
		style.setWrapText(false);//設定文字是否換行
		//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
		styles.put("cell_number_total", style);

		return styles;
	}


	/**
	 * 6001畫面查詢
	 * 取得各通路已撥款總計
	 * @author Jeff  Gu 		
	 * @param group
	 * @param channels
	 * @param dateFrom
	 * @param dateTo
	 * @param gueryGroupType 群組類別
	 * @param transfered  是否撥款
	 * @param paginationConfig
	 * @return
	 */
	public PaginatedList<Map<String, Object>> queryTransferLogByGroup(Group group, String channels[],
			Date[] dateFrom, Date[] dateTo,List<String> gueryGroupType,String transfered,PaginatedList<Map<String, Object>> paginationConfig) {

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		PaginatedList<Map<String, Object>> fbLogs = soez6001Dao.queryTransferLogByGroup(group, channels, dateFrom, dateTo, gueryGroupType, transfered, paginationConfig);

		if(fbLogs.getPaginatedList()== null || fbLogs.getPaginatedList().size() == 0)
			return null;

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		/* 設定複製用PaginatedList 再對資料作處理 不然 foreach的狀況下不能對來源檔案作操作  */
		PaginatedList<Map<String, Object>> results = new PaginatedList<Map<String, Object>>();				
		results.setFullListSize(fbLogs.getFullListSize());
		results.setPageSize(fbLogs.getPageSize());
		results.setPageNumber(fbLogs.getPageNumber());
		results.setSortCriterion(fbLogs.getSortCriterion());
		results.setSortDirection(fbLogs.getSortDirection());
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);


		List<Map<String, Object>> temp = new ArrayList<Map<String, Object>>();
		for(Map<String, Object> fblog:fbLogs.getPaginatedList())
		{	

			//個別Code統計值
			Long codeCount = new Long(0);
			Long codeAmount = new Long(0);


			//計算手續費
			codeAmount = ((BigDecimal)fblog.get("realAmount")).longValue();// REAL_AMOUNT
			codeCount = ((BigDecimal)fblog.get("totalCount")).longValue();//TOTAL_COUNT

			//加統計值
			totalCount += codeCount;
			totalAmount += codeAmount;

			fblog.put("allCount", totalCount);
			fblog.put("allAmount", totalAmount);

			/*計算總計*/
			temp.add(fblog);
		}


		results.setPaginatedList(temp);
		/* 清除資源*/
		fbLogs.getPaginatedList().clear();
		fbLogs=null;

		return results;
	}




	/**
	 * 20130221 6001畫面查詢
	 * 取得各通路未撥款總計
	 * @author Jeff  Gu 		
	 * @param group
	 * @param channels
	 * @param dateFrom
	 * @param dateTo
	 * @param gueryGroupType 群組類別
	 * @param transfered  是否撥款
	 * @param paginationConfig
	 * @return
	 */
	public PaginatedList<Map<String, Object>> queryNonTransferLogByGroup(Group group, String channels[],
			Date[] dateFrom, Date[] dateTo,List<String> gueryGroupType,String transfered,PaginatedList<Map<String, Object>> paginationConfig) {

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		PaginatedList<Map<String, Object>> fbLogs = soez6001Dao.queryNonTransferLogByGroup(group, channels, dateFrom, dateTo, gueryGroupType, transfered, paginationConfig);

		if(fbLogs.getPaginatedList()== null || fbLogs.getPaginatedList().size() == 0)
			return null;

		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		/* 設定複製用PaginatedList 再對資料作處理 不然 foreach的狀況下不能對來源檔案作操作  */
		PaginatedList<Map<String, Object>> results = new PaginatedList<Map<String, Object>>();				
		results.setFullListSize(fbLogs.getFullListSize());
		results.setPageSize(fbLogs.getPageSize());
		results.setPageNumber(fbLogs.getPageNumber());
		results.setSortCriterion(fbLogs.getSortCriterion());
		results.setSortDirection(fbLogs.getSortDirection());
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		Long totalCount = new Long(0);
		Long totalAmount = new Long(0);


		List<Map<String, Object>> temp = new ArrayList<Map<String, Object>>();
		for(Map<String, Object> fblog:fbLogs.getPaginatedList())
		{	

			//個別Code統計值
			Long codeCount = new Long(0);
			Long codeAmount = new Long(0);


			//計算手續費
			codeAmount = ((BigDecimal)fblog.get("realAmount")).longValue();// REAL_AMOUNT
			codeCount = ((BigDecimal)fblog.get("totalCount")).longValue();//TOTAL_COUNT

			//加統計值
			totalCount += codeCount;
			totalAmount += codeAmount;

			fblog.put("allCount", totalCount);
			fblog.put("allAmount", totalAmount);

			/*計算總計*/
			temp.add(fblog);
		}


		results.setPaginatedList(temp);
		/* 清除資源*/
		fbLogs.getPaginatedList().clear();
		fbLogs=null;

		return results;
	}



	/**
	 *  2013/02/23
	 * 取得各通路未撥款總計  顯示畫面excel (可分群組)
	 * @author Jeff  Gu 
	 */
	@Override
	public String doExportNonTransferStatisticsXls(Group group, String[] channels,
			Date[] dateFroms, Date[] dateTos,List<String> gueryGroupType,String transfered) {

		if(null==group)
			group=new Group();

		if (!UserUtil.isBankGroup())
		{
			group=UserUtil.getUserGroup();
		}

		Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可
		Map<String, CellStyle> styles = createStyles(wb);

		Sheet sheet = wb.createSheet("超商撥款統計表");
		PrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
		sheet.setZoom(3, 4);
		sheet.setColumnWidth(0, 256*25);
		sheet.setColumnWidth(1, 256*10);
		sheet.setColumnWidth(3, 256*20);
		sheet.setColumnWidth(4, 256*20);
		sheet.setColumnWidth(5, 256*20);
		sheet.setColumnWidth(6, 256*20);


		// 行數
		int rownum = 0;

		//title row
		Row titleRow = sheet.createRow(rownum++);
		titleRow.setHeightInPoints((float)32.25);
		Cell titleCell = titleRow.createCell(0);
		titleCell.setCellValue("超商／郵局撥款統計表");
		titleCell.setCellStyle(styles.get("title"));
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1"));

		logger.debug("超商／郵局撥款統計表 {}",rownum);

		Row blankRow = sheet.createRow(rownum++);
		blankRow.setHeightInPoints((float)18);

		logger.debug("blankRow {}",rownum);

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		for(int i=0;i<channels.length;i++)
		{
			String channel = channels[i];
			Date dateFrom = dateFroms[i];
			Date dateTo = dateTos[i];
			if(StringUtils.isNotBlank(channel))
			{
				Row dataRow = sheet.createRow(rownum++);
				Cell dataCell;
				dataCell = dataRow.createCell(0);
				dataCell.setCellValue(CodeTableUtil.getInstance().getCodeName("CHANEL", channel)  + "：" + dateFormat.format(dateFrom)+"～" + dateFormat.format(dateTo));
				dataCell.setCellStyle(styles.get("title2"));
				sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$H$" + rownum));
			}

		}

		logger.debug("channels {}",rownum);

		//串欄位表頭
		String[] titles = {"NO",
				"撥款日期","業者代號","實體帳號","業者名稱","通路" ,"代收筆數", "入帳金額"};

		Row headerRow = sheet.createRow(rownum++);
		headerRow.setHeightInPoints((float) 25.5);
		Cell headerCell;

		logger.debug("titles {}",rownum);

		for (int i = 0; i < titles.length; i++) {
			headerCell = headerRow.createCell(i);
			headerCell.setCellValue(titles[i]);
			headerCell.setCellStyle(styles.get("header"));
		}

		//總計數據
		Long totalCount = new Long(0);
		Long totalRealAmount = new Long(0);


		PaginatedList<Map<String, Object>>queryResult = new PaginatedList<Map<String,Object>>();
		/* 保持顯示全部不分頁*/
		queryResult.setPageSize(-1);

		PaginatedList<Map<String, Object>> result = soez6001Dao.queryNonTransferLogByGroup(group, channels, dateFroms, dateTos, gueryGroupType, transfered,queryResult);


		/*產生傳輸日期區間*/
		compareDate(channels,dateFroms, dateTos );

		//動態回圈
		List<Map<String, Object>> fbLogs = result.getPaginatedList();


		int no=0;//序號

		if (null != fbLogs && 0 != fbLogs.size())
		{	int cellRangeAddressStart=0;
		for(Map<String, Object> channelData : fbLogs)
		{	

			no++;

			int cellRangeAddressEnd = 0;
			cellRangeAddressStart=rownum+1;	
			for(int i=0;i<channels.length;i++)
			{
				String channel = channels[i];
				if(StringUtils.isNotBlank(channel))
				{

					Row dataRow = null; 
					Cell cell = null;
					//int mergeRownum = sheet.getLastRowNum() + 1;
					/*計算合併欄位的位置*/



					cellRangeAddressEnd = 0;

					/* 一圈就是一個日期區間的資料 */


					//Date transferTime = (Date)channelData.get("transferTime");//撥款日期
					String groupNo = (String)channelData.get("groupNo");//業者代號
					String  accountNo= (String)channelData.get("accountNo");//實體帳號
					String  groupName= (String)channelData.get("groupName");//業者名稱
					//String channel = (String)channelData.get("CHANNEL");        	
					//String channelName = (String)channelData.get("CHANNEL_NAME");//通路名稱



					dataRow = sheet.createRow(rownum++);
					if (0>= cellRangeAddressEnd)
						cellRangeAddressEnd = rownum;
					dataRow.setHeightInPoints((float) 25);


					cell = dataRow.createCell(0);//序號
					cell.setCellValue(no);
					cell.setCellStyle(styles.get("cell"));


					cell = dataRow.createCell(1);//撥款日期
					if (qryDateMin==null||qryDateMax==null)
						cell.setCellValue("N/A");
					else
						cell.setCellValue(dateFormat.format(qryDateMin)+"～" + dateFormat.format(qryDateMax));
					cell.setCellStyle(styles.get("cell_date"));

					cell = dataRow.createCell(2);//業者代號
					cell.setCellValue(groupNo);
					cell.setCellStyle(styles.get("cell"));

					cell = dataRow.createCell(3);//實體帳號
					cell.setCellValue(accountNo);
					cell.setCellStyle(styles.get("cell"));

					cell = dataRow.createCell(4);//業者名稱
					cell.setCellValue(groupName);
					cell.setCellStyle(styles.get("cell"));





					/*超商欄位*/
					String  codeName = CodeTableUtil.getInstance().getCodeName("CHANEL", channel) ;//通路名稱

					// 沒有的不顯示
					int channelCount = (int)((BigDecimal) channelData.get(channel+"Count")).longValue();
					Long channelAmount = ((BigDecimal)channelData.get(channel+"Amount")).longValue();


					cell = dataRow.createCell(5);
					cell.setCellValue(codeName);
					cell.setCellStyle(styles.get("cell"));

					if(channelCount>0){
						cell = dataRow.createCell(6);
						cell.setCellValue(channelCount);
						cell.setCellStyle(styles.get("cell"));

						cell = dataRow.createCell(7);
						cell.setCellValue(channelAmount);
						cell.setCellStyle(styles.get("cell_number"));
					}else{
						cell = dataRow.createCell(6);

						cell.setCellValue("查無資料 ");
						cell.setCellStyle(styles.get("cell"));
						cell = dataRow.createCell(7);
						cell.setCellStyle(styles.get("cell"));
						sheet.addMergedRegion(CellRangeAddress.valueOf("$G$"+rownum+":$H$" + rownum));

					}


				}



			}

			/* 合併前五格*/
			String[] mergeRow={"A","B","C","D","E"};	

			for(int i=0;i<=4;i++){
				//ex:"$A$"+rownum+":$I$" + rownum  "A$9"
				//sheet.addMergedRegion(CellRangeAddress.valueOf("$"+mergeRow[0]+"$"+cellRangeAddress+":$"+mergeRow[0]+"$"+(cellRangeAddress+lastIndex)));            
				sheet.addMergedRegion(CellRangeAddress.valueOf("$"+mergeRow[i]+"$"+(cellRangeAddressStart)+":$"+mergeRow[i]+"$"+(cellRangeAddressEnd)));            
				logger.debug("cellRangeAddressStart {}",cellRangeAddressStart);	
				logger.debug("cellRangeAddressEnd {}",cellRangeAddressEnd);
			}



			/* 統計*/
			Row dataCountRow = sheet.createRow(rownum++);
			Cell dataCell;
			dataCell = dataCountRow.createCell(0);
			dataCell.setCellValue("統計");
			dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
			dataCell = dataCountRow.createCell(1);
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataCountRow.createCell(2);
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataCountRow.createCell(3);	
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataCountRow.createCell(4);	
			dataCell.setCellStyle(styles.get("cell"));
			dataCell = dataCountRow.createCell(5);				
			dataCell.setCellStyle(styles.get("cell"));

			int groupTotalCount =(int)((BigDecimal) channelData.get("totalCount")).longValue();
			Long groupRealAmount = ((BigDecimal)channelData.get("realAmount")).longValue();

			dataCell = dataCountRow.createCell(6);
			dataCell.setCellValue(groupTotalCount);
			dataCell.setCellStyle(styles.get("cell"));

			dataCell = dataCountRow.createCell(7);
			dataCell.setCellValue(groupRealAmount);
			dataCell.setCellStyle(styles.get("cell_number"));

			/* 統計所有數據 */
			totalCount += groupTotalCount;
			totalRealAmount += groupRealAmount;



			//合併
			sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$F$" + rownum));

		}

		}

		/* 總計*/
		Row dataRow = sheet.createRow(rownum++);
		Cell dataCell;
		sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$D$" + rownum));
		dataCell = dataRow.createCell(0);
		dataCell.setCellValue("統計");
		dataCell.setCellStyle(styles.get("cell_ALIGN_RIGHT"));
		dataCell = dataRow.createCell(1);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(2);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(3);	
		dataCell.setCellStyle(styles.get("cell"));

		dataCell = dataRow.createCell(4);				
		dataCell.setCellValue("超商／郵局合計");
		dataCell.setCellStyle(styles.get("cell"));

		dataCell = dataRow.createCell(5);				
		dataCell.setCellStyle(styles.get("cell"));
		/*  總計記數 */		
		dataCell = dataRow.createCell(6);
		dataCell.setCellValue(totalCount);
		dataCell.setCellStyle(styles.get("cell"));
		dataCell = dataRow.createCell(7);
		dataCell.setCellValue(totalRealAmount);
		dataCell.setCellStyle(styles.get("cell_number"));
		int colCount = 0;
		/* 效能提升  超商／郵局傳輸統計查詢  總計記數 */
		while (colCount <= titles.length) {
			sheet.autoSizeColumn((short) colCount++,true);//自動調整欄寬
		}

		String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
		File directory = new File(tempDownloadFilePath);             
		if(!directory.exists())
		{
			try {
				FileUtils.forceMkdir(directory);
			}
			catch (IOException e) {
				logger.error("{}",e);
			}
		}

		String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-超商傳輸統計表.xls"; //

		FileOutputStream out;
		try {
			out = new FileOutputStream(fileFullName);
			wb.write(out);
			out.close();
		}
		catch (FileNotFoundException e) {
			logger.error("{}",e);
		}
		catch (IOException e) {
			logger.error("{}",e);
		}
		return fileFullName;
	}



	/**
	 * 產生畫面傳輸日期區間,並去除跳號
	 * @param qryDateFroms
	 * @param qryDateTos
	 */
	private void compareDate(String[] channels,Date [] qryDateFroms, Date [] qryDateTos )
	{

		qryDateMin=qryDateFroms[0];
		qryDateMax=qryDateTos[0];
		for(int i=1;i<qryDateFroms.length;i++)
		{			
			Date dateFrom = qryDateFroms[i];
			String channel = channels[i];
			if(StringUtils.isNotBlank(channel))
			{
				if(null!= dateFrom){
					if(dateFrom.before(qryDateMin))
						qryDateMin=dateFrom;	
				}
			}

		}	

		for(int i=1;i<qryDateTos.length;i++)
		{			
			Date dateTo = qryDateTos[i];
			String channel = channels[i];
			if(StringUtils.isNotBlank(channel))
			{
				if(null!= dateTo){
					if(dateTo.after(qryDateMax))
						qryDateMax=dateTo;	
				}
			}
		}

	}



}
